Skip to main content
Version: Upcoming

AtsDegrossRevConGateway

V8 Message Definiton

Records inserted, updated, or replaced into the AtsDegrossRevConGateway table are validated and then converted to SpdrParentOrder records and forwarded to the dedicated (non-striped) degrossing execution engine for auction processing. Each record represents one order line: a single ticker/expiry RevCon spread (call + put + stock legs). Strike is SR-controlled (published in AtsDegrossRevConSpreads); not a client input. The groupingCode is client-supplied and used as the SpreadBroker cache key on the EE; lines are independent. See DEGROSSING_PLAN_2.1.md for the full auction algorithm specification.

METADATA​

AttributeValue
Topic5120-srse-gateway
MLink TokenClientTrading
ProductSRTrade
accessTypeSELECT,UPDATE(spdrActionType),INSERT,DELETE
MLink EndpointMLink-Live

Table Definition​

FieldTypeKeyDefault ValueComment
ticker_atenum - AssetTypePRI'None'underlier ticker eg AAPL TSLA
ticker_tsenum - TickerSrcPRI'None'underlier ticker eg AAPL TSLA
ticker_tkVARCHAR(12)PRI''underlier ticker eg AAPL TSLA
expiryDATEPRI'1900-01-01'flex option expiry date for this RevCon spread
accntVARCHAR(16)PRI''SR trading account
sideenum - BuySellPRI'None'Buy reversal lend stock Sell conversion borrow stock
clientFirmVARCHAR(16)PRI''
groupingCodeCHAR(19)PRI'0000-0000-0000-0000'clientsupplied order identifier used as SpreadBroker key for cancelreplace
spdrActionTypeenum - SpdrActionType'Add'Add AddReplace Cancel
sizeINT0number of RevCon units 1 unit 100 shares
useSRRateenum - YesNo'Yes'Yes use SR reference rate No use clientRate
clientRateDOUBLE0client financing rate only used if useSRRateNo
useSRSDivenum - YesNo'Yes'Yes use SR reference sdiv No use clientSDiv
clientSDivDOUBLE0client continuous dividend rate only used if useSRSDivNo
useSRDDivenum - YesNo'Yes'Yes use SR dividend schedule No use ClientDDiv set
runAuctionNowenum - YesNo'No'Yes trigger RunFinalAuction immediately Mars testing resets to No after firing
runTrialNowenum - YesNo'No'Yes run an ondemand TRIAL immediately full solve cross logging provisional results NONbinding orders are NOT closed nothing submitted Repeatable For testing
gatewayStatusenum - AtsRevConGatewayStatus'None'gateway processing status
rejectReasonTINYTEXT''if rejected the reason why
parentNumberBIGINT0assigned SpdrParentOrder number if accepted
orderDttmDATETIME(6)'1900-01-01 00:00:00.000000'order entry timestamp
checksumSMALLINT13standard gateway checksum must be set to 13
userNameVARCHAR(24)''set by server from context
timestampDATETIME(6)'1900-01-01 00:00:00.000000'
ClientDDivListJSON'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)​

FieldSequence
ticker_tk1
ticker_at2
ticker_ts3
expiry4
accnt5
side6
clientFirm7
groupingCode8

JSON Block (ClientDDivList)​

FieldTypeComment
ddivExDateenum - ddivExDateexpected exdate
ddivAmountenum - ddivAmountexpected dividend amount per share

CREATE TABLE EXAMPLE QUERY​

CREATE TABLE `SRTrade`.`MsgAtsDegrossRevConGateway` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY','RATE') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker (e.g., AAPL, TSLA)',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','SCE','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFEC','ICEFEF','CEQT','TSX','TMX') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker (e.g., AAPL, TSLA)',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier ticker (e.g., AAPL, TSLA)',
    `expiry` DATE NOT NULL DEFAULT '1900-01-01' COMMENT 'flex option expiry date for this RevCon spread',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR trading account',
    `side` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None' COMMENT 'Buy = reversal (lend stock); Sell = conversion (borrow stock)',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'client-supplied order identifier; used as SpreadBroker key for cancel/replace',
    `spdrActionType` ENUM('Add','AddReplace','Replace','Cancel','Modify') NOT NULL DEFAULT 'Add' COMMENT 'Add, AddReplace, Cancel',
    `size` INT NOT NULL DEFAULT 0 COMMENT 'number of RevCon units (1 unit = 100 shares)',
    `useSRRate` ENUM('None','Yes','No') NOT NULL DEFAULT 'Yes' COMMENT 'Yes = use SR reference rate; No = use clientRate',
    `clientRate` DOUBLE NOT NULL DEFAULT 0 COMMENT 'client financing rate (only used if useSRRate=No)',
    `useSRSDiv` ENUM('None','Yes','No') NOT NULL DEFAULT 'Yes' COMMENT 'Yes = use SR reference sdiv; No = use clientSDiv',
    `clientSDiv` DOUBLE NOT NULL DEFAULT 0 COMMENT 'client continuous dividend rate (only used if useSRSDiv=No)',
    `useSRDDiv` ENUM('None','Yes','No') NOT NULL DEFAULT 'Yes' COMMENT 'Yes = use SR dividend schedule; No = use ClientDDiv set',
    `runAuctionNow` ENUM('None','Yes','No') NOT NULL DEFAULT 'No' COMMENT 'Yes = trigger RunFinalAuction immediately (Mars testing); resets to No after firing',
    `runTrialNow` ENUM('None','Yes','No') NOT NULL DEFAULT 'No' COMMENT 'Yes = run an on-demand TRIAL immediately: full solve + cross logging + provisional results, NON-binding (orders are NOT closed, nothing submitted). Repeatable. For testing.',
    `gatewayStatus` ENUM('None','Pending','Accepted','Rejected') NOT NULL DEFAULT 'None' COMMENT 'gateway processing status',
    `rejectReason` TINYTEXT NOT NULL DEFAULT '' COMMENT 'if rejected, the reason why',
    `parentNumber` BIGINT NOT NULL DEFAULT 0 COMMENT 'assigned SpdrParentOrder number (if accepted)',
    `orderDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'order entry timestamp',
    `checksum` SMALLINT NOT NULL DEFAULT 13 COMMENT 'standard gateway checksum; must be set to 13',
    `userName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'set by server from context',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `ClientDDivList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ClientDDivList)),
    CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`expiry`,`accnt`,`side`,`clientFirm`,`groupingCode`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='Records inserted, updated, or replaced into the AtsDegrossRevConGateway table are validated and then converted to SpdrParentOrder records\nand forwarded to the dedicated (non-striped) degrossing execution engine for auction processing.\nEach record represents one order line: a single ticker/expiry RevCon spread (call + put + stock legs).\nStrike is SR-controlled (published in AtsDegrossRevConSpreads); not a client input.\nThe groupingCode is client-supplied and used as the SpreadBroker cache key on the EE; lines are independent.\nSee DEGROSSING_PLAN_2.1.md for the full auction algorithm specification.';

SELECT TABLE EXAMPLE QUERY​

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `expiry`,
    `accnt`,
    `side`,
    `clientFirm`,
    `groupingCode`,
    `spdrActionType`,
    `size`,
    `useSRRate`,
    `clientRate`,
    `useSRSDiv`,
    `clientSDiv`,
    `useSRDDiv`,
    `runAuctionNow`,
    `runTrialNow`,
    `gatewayStatus`,
    `rejectReason`,
    `parentNumber`,
    `orderDttm`,
    `checksum`,
    `userName`,
    `timestamp`,
    `ClientDDivList`
FROM `SRTrade`.`MsgAtsDegrossRevConGateway`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY','RATE') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','SCE','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFEC','ICEFEF','CEQT','TSX','TMX') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a DATE */
    `expiry` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','Buy','Sell') */ 
    `side` = 'None'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm'
  AND
    /* Replace with a CHAR(19) */
    `groupingCode` = 'Example_groupingCode';

INSERT TABLE EXAMPLE QUERY​

INSERT INTO `SRTrade`.`MsgAtsDegrossRevConGateway`(
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY','RATE') */ 
    `ticker_at`,
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','SCE','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFEC','ICEFEF','CEQT','TSX','TMX') */ 
    `ticker_ts`,
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk`,
    /* Replace with a DATE */
    `expiry`,
    /* Replace with a VARCHAR(16) */ 
    `accnt`,
    /* Replace with a ENUM('None','Buy','Sell') */ 
    `side`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a CHAR(19) */
    `groupingCode`,
    /* Replace with a ENUM('Add','AddReplace','Replace','Cancel','Modify') */ 
    `spdrActionType`,
    /* Replace with a INT */ 
    `size`,
    /* Replace with a ENUM('None','Yes','No') */
    `useSRRate`,
    /* Replace with a DOUBLE */ 
    `clientRate`,
    /* Replace with a ENUM('None','Yes','No') */
    `useSRSDiv`,
    /* Replace with a DOUBLE */ 
    `clientSDiv`,
    /* Replace with a ENUM('None','Yes','No') */
    `useSRDDiv`,
    /* Replace with a ENUM('None','Yes','No') */
    `runAuctionNow`,
    /* Replace with a ENUM('None','Yes','No') */
    `runTrialNow`,
    /* Replace with a ENUM('None','Pending','Accepted','Rejected') */ 
    `gatewayStatus`,
    /* Replace with a TINYTEXT */ 
    `rejectReason`,
    /* Replace with a BIGINT */ 
    `parentNumber`,
    /* Replace with a DATETIME(6) */
    `orderDttm`,
    /* Replace with a SMALLINT */
    `checksum`,
    /* Replace with a VARCHAR(24) */ 
    `userName`,
    /* Replace with a DATETIME(6) */
    `timestamp`,
    /* Replace with a JSON */
    `ClientDDivList`
) 
VALUES(
    'None',
    'None',
    'Example_ticker_tk',
    '2022-01-01',
    'Example_accnt',
    'None',
    'Example_clientFirm',
    'Example_groupingCode',
    'Add',
    5,
    'Yes',
    4.56,
    'Yes',
    4.56,
    'Yes',
    'No',
    'No',
    'None',
    'dummy tiny text',
    1234567890,
    '2022-01-01 12:34:56.000000',
    13,
    'Example_userName',
    '2022-01-01 12:34:56.000000',
    '{"key": "value"}'
);

DELETE TABLE EXAMPLE QUERY​

DELETE FROM `SRTrade`.`MsgAtsDegrossRevConGateway` 
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY','RATE') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','SCE','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFEC','ICEFEF','CEQT','TSX','TMX') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a DATE */
    `expiry` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','Buy','Sell') */ 
    `side` = 'None'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm'
  AND
    /* Replace with a CHAR(19) */
    `groupingCode` = 'Example_groupingCode';

Doc Columns Query​

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='AtsDegrossRevConGateway' ORDER BY ordinal_position ASC;